import java.sql.*;
import org.apache.commons.cli.*;
import java.util.ArrayList;
import java.util.List;

// company_query -q <Number> -p Parameter
public class company_query {
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";

    static final String DB_URL = "jdbc:mysql://localhost:3306/COMPANY?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true";

    static final String USER = "root";

    static final String PASS = "123456";

    public static void main(String[] args) throws ClassNotFoundException, SQLException, ParseException {
        // 创建命令行解析项
        Options options = new Options();
        // 添加选项：Number 问题序号
        Option question = new Option("q", "question", true, "选择问题序号");
        options.addOption(question);
        // 添加选项：Parameters
        Option parameters = new Option("p", "parameters", true, "问题参数");
        options.addOption(parameters);

        // 解析命令行参数
        CommandLineParser parser = new DefaultParser();
        CommandLine cmd = parser.parse(options, args);

        if (cmd.hasOption("q") && cmd.hasOption("p")) {
            String questionNumber = cmd.getOptionValue("q");
            String params = cmd.getOptionValue("p");

            System.out.println(String.format("选择了第%s题", questionNumber));

            Connection conn = null;
            Statement stmt = null;
    
            Class.forName(JDBC_DRIVER);
    
            System.out.println("connecting database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
    
            System.out.println("initiating a statement...");
            stmt = conn.createStatement();

            List<String> result = new ArrayList<>();
            switch (questionNumber) {
                case "1":
                    result = question1(params, stmt);
                    break;
                case "2":
                    result = question2(params, stmt);
                    break;
                case "3":
                    result = question3(params, stmt);
                    break;
                case "4":
                    result = question4(params, args[4], stmt);
                    break;
                case "5":
                    result = question5(params, stmt);
                    break;
                case "6":
                    result = question6(params, stmt);
                    break;
                case "7":
                    result = question7(params, args[4], stmt);
                    break;
                case "8":
                    result = question8(params, stmt);
                    break;
                case "9":
                    result = question9(params, args[4], stmt);
                    break;
                default:
                    break;
            }
            System.out.println("输出结果:");
            for (String item : result) {
                System.out.println(item);
            }
        }
    }

    /**
     * 1:参加了项目编号为%PNO%的项目的员工号，其中%PNO%为 Java 语言编写 的程序的输入参数;
     * @example java -classpath ./lib/commons-cli-1.9.0.jar:./lib/mysql-connector-java-8.0.11.jar  company_query.java -q 1 -p P001
     */
    static List<String> question1(String pno, Statement stmt) throws SQLException {
        List<String> result = new ArrayList<>();

        String sql = """
                SELECT e.essn FROM employee e 
                JOIN works_on w ON w.essn = e.essn
                JOIN project p ON w.pno = p.pno 
                WHERE p.pno = "%s";
                """;
        ResultSet rs = stmt.executeQuery(String.format(sql, pno));

        while (rs.next()) {
            result.add(rs.getString("essn"));
        }

        return result;
    }

    /**
     * 2:参加了项目名为%PNAME%的员工名字，其中%PNAME%为 Java 语言编写 的程序的输入参数;
     * @example java -classpath ./lib/commons-cli-1.9.0.jar:./lib/mysql-connector-java-8.0.11.jar  company_query.java -q 2 -p "SQL Project"
     */
    static List<String> question2(String pname, Statement stmt) throws SQLException {
        List<String> result = new ArrayList<>();

        String sql = """
                SELECT e.ename FROM employee e 
                JOIN works_on w ON w.essn = e.essn
                JOIN project p ON w.pno = p.pno 
                WHERE p.pname = "%s";
                """;
        ResultSet rs = stmt.executeQuery(String.format(sql, pname));

        while (rs.next()) {
            result.add(rs.getString("ename"));
        }

        return result;
    }

    /**
     * 3:在%DNAME%工作的所有工作人员的名字和地址，其中%DNAME%为 Java 语言编写的程序的输入参数;
     * @example java -classpath ./lib/commons-cli-1.9.0.jar:./lib/mysql-connector-java-8.0.11.jar  company_query.java -q 3 -p "Research Department"
     */
    static List<String> question3(String dname, Statement stmt) throws SQLException {
        List<String> result = new ArrayList<>();

        String sql = """
                SELECT e.ename, e.address FROM employee e JOIN department d ON e.dno = d.dno WHERE d.dname = "%s";
                """;
        ResultSet rs = stmt.executeQuery(String.format(sql, dname));

        while (rs.next()) {
            String ename = rs.getString("ename");
            String address = rs.getString("address");
            result.add(ename + "\t" + address);
        }

        return result;
    }

    // 
    /**
     * 4:在%DNAME%工作且工资低于%SALARY%元的员工名字和地址，其 中%DNAME%和%SALARY%为 Java 语言编写的程序的输入参数;
     * @example java -classpath ./lib/commons-cli-1.9.0.jar:./lib/mysql-connector-java-8.0.11.jar  company_query.java -q 4 -p "Research Department" 9000
     */
    static List<String> question4(String dname, String salary, Statement stmt) throws SQLException {
        List<String> result = new ArrayList<>();

        String sql = """
                SELECT e.ename, e.address FROM employee e JOIN department d ON e.dno = d.dno WHERE d.dname = "%s" and e.salary < %s;
                """;
        ResultSet rs = stmt.executeQuery(String.format(sql, dname, salary));

        while (rs.next()) {
            String ename = rs.getString("ename");
            String address = rs.getString("address");
            result.add(ename + "\t" + address);
        }

        return result;
    }

    /**
     * 5:没有参加项目编号为%PNO%的项目的员工姓名，其中%PNO%为 Java 语言 编写的程序的输入参数;
     * @example java -classpath ./lib/commons-cli-1.9.0.jar:./lib/mysql-connector-java-8.0.11.jar  company_query.java -q 5 -p P001
     */
    static List<String> question5(String pno, Statement stmt) throws SQLException {
        List<String> result = new ArrayList<>();

        String sql = """
                SELECT e.ename FROM employee e 
                JOIN works_on w ON w.essn = e.essn
                JOIN project p ON w.pno = p.pno 
                WHERE p.pno != "%s";
                """;
        ResultSet rs = stmt.executeQuery(String.format(sql, pno));

        while (rs.next()) {
            result.add(rs.getString("ename"));
        }

        return result;
    }

    /**
     * 6:由%ENAME%领导的工作人员的姓名和所在部门的名字，其中%ENAME%为 Java 语言编写的程序的输入参数;
     * @example java -classpath ./lib/commons-cli-1.9.0.jar:./lib/mysql-connector-java-8.0.11.jar  company_query.java -q 6 -p Messi
     */
    static List<String> question6(String ename, Statement stmt) throws SQLException {
        List<String> result = new ArrayList<>();

        String sql = """
            SELECT e2.ename, d.dname 
            FROM employee e1 
            JOIN employee e2 ON e1.essn = e2.superssn 
            JOIN department d ON e2.dno = d.dno 
            WHERE e1.ename = "%s";
            """;
        ResultSet rs = stmt.executeQuery(String.format(sql, ename));

        while (rs.next()) {
            String ename2 = rs.getString("ename");
            String dname = rs.getString("dname");
            result.add(ename2 + "\t" + dname);
        }

        return result;
    }

    /**
     * 7:至少参加了项目编号为%PNO1%和%PNO2%的项目的员工号，其中%PNO1% 和%PNO2%为 Java 语言编写的程序的输入参数;
     * @example java -classpath ./lib/commons-cli-1.9.0.jar:./lib/mysql-connector-java-8.0.11.jar  company_query.java -q 7 -p P001 P002
     */
    static List<String> question7(String pno1, String pno2, Statement stmt) throws SQLException {
        List<String> result = new ArrayList<>();

        String sql =  """
            SELECT DISTINCT w1.essn
            FROM works_on w1
            JOIN works_on w2 ON w1.essn = w2.essn
            WHERE w1.pno = "%s" 
            AND w2.pno = "%s";
            """;
        ResultSet rs = stmt.executeQuery(String.format(sql, pno1, pno2));

        while (rs.next()) {
            result.add(rs.getString("essn"));
        }

        return result;
    }

    /**
     * 8:员工平均工资低于%SALARY%元的部门名称，其中%SALARY%为 Java 语言编写的程序的输入参数;
     * @example java -classpath ./lib/commons-cli-1.9.0.jar:./lib/mysql-connector-java-8.0.11.jar company_query.java -q 8 -p 9000
     */
    static List<String> question8(String salary, Statement stmt) throws SQLException {
        List<String> result = new ArrayList<>();

        String sql = """
                SELECT d.dname, AVG(e.salary) as avg_salary
                FROM department d
                JOIN employee e ON d.dno = e.dno
                GROUP BY d.dno, d.dname
                HAVING AVG(e.salary) < %s
                ORDER BY avg_salary;
                """;
        
        ResultSet rs = stmt.executeQuery(String.format(sql, salary));

        while (rs.next()) {
            String dname = rs.getString("dname");
            double avgSalary = rs.getDouble("avg_salary");
            result.add(String.format("%s (平均工资: %.2f)", dname, avgSalary));
        }

        return result;
    }

    /**
     * 9:至少参与了%N%个项目且工作总时间不超过%HOURS%小时的员工名字，其中%N%和%HOURS%为 Java 语言编写的程序的输入参数;
     * @example java -classpath ./lib/commons-cli-1.9.0.jar:./lib/mysql-connector-java-8.0.11.jar company_query.java -q 9 -p 2 10
     */
    static List<String> question9(String n, String hours, Statement stmt) throws SQLException {
        List<String> result = new ArrayList<>();

        String sql =  """
                SELECT e.ename, COUNT(DISTINCT w.pno) as project_count, SUM(w.hours) as total_hours
                FROM employee e
                JOIN works_on w ON e.essn = w.essn
                GROUP BY e.essn, e.ename
                HAVING COUNT(DISTINCT w.pno) >= %s
                AND SUM(w.hours) <= %s
                ORDER BY total_hours;
                """;
        ResultSet rs = stmt.executeQuery(String.format(sql, n, hours));

        while (rs.next()) {
            String ename = rs.getString("ename");
            int pCount = rs.getInt("project_count");
            double totalHours = rs.getDouble("total_hours");
            result.add(String.format("%s (项目数: %d, 总工时: %.2f)", ename, pCount, totalHours));
        }

        return result;
    }
}